﻿Imports MICROFORMA.BEL
Imports MICROFORMA.DIL
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports System.Data.Common

Public Class ClienteDA
    Implements ClienteDI


    Public Function Add(ByVal _cliente_BE As BEL.ClienteBE) As String Implements DIL.ClienteDI.Add
        Dim db As Database = DatabaseFactory.CreateDatabase()

        Try
            Dim query As String = "insert into clientes values (Nombre, Direccion, FechaCreacion, FechaModificacion, Estado)" _
                                    & "Values (" & _cliente_BE.Nombre & ",'" & _cliente_BE.Nombre _
                                    & "','" & _cliente_BE.Direccion & "',getdate(),null,0)"


            Dim o_dbCommand As DbCommand = db.GetSqlStringCommand(query)


            db.ExecuteNonQuery(o_dbCommand)


            Return "OK"

        Catch ex As Exception

            Return ex.Message.ToString

        End Try
    End Function

    Public Function Delete(ByVal _cliente_BE As BEL.ClienteBE) As String Implements DIL.ClienteDI.Delete
        Dim db As Database = DatabaseFactory.CreateDatabase()

        Try
            Dim query As String = "Update Cliente Set estado='" & _cliente_BE.Estado _
                                 & "',FechaModificacion=" & "getDate()" _
                                 & " where IdCliente=" & _cliente_BE.IdCliente


            Dim o_dbCommand As DbCommand = db.GetSqlStringCommand(query)


            db.ExecuteNonQuery(o_dbCommand)


            Return "OK"

        Catch ex As Exception

            Return ex.Message.ToString

        End Try
    End Function

    Public Function Edit(ByVal _cliente_BE As BEL.ClienteBE) As String Implements DIL.ClienteDI.Edit
        Dim db As Database = DatabaseFactory.CreateDatabase()

        Try
            Dim query As String = "Update Cliente Set Nombre=" & _cliente_BE.Nombre _
                                 & ",FechaModificacion=" & "getDate()" _
                                 & " where IdCliente=" & _cliente_BE.IdCliente


            Dim o_dbCommand As DbCommand = db.GetSqlStringCommand(query)


            db.ExecuteNonQuery(o_dbCommand)


            Return "OK"

        Catch ex As Exception

            Return ex.Message.ToString

        End Try
    End Function

    Public Function getIdLastRecord() As Integer Implements DIL.ClienteDI.getIdLastRecord
        Dim idSolicitud As Integer = 0

        Dim db As Database = DatabaseFactory.CreateDatabase

        Dim query As String = "select max(IdSolicitud) from Solicitud"

        Dim o_dbCommand As DbCommand = db.GetSqlStringCommand(query)

        Dim o_Cnx As DbConnection = db.CreateConnection()

        o_Cnx.Open()

        Dim o_Transaction As DbTransaction = o_Cnx.BeginTransaction

        Try
            idSolicitud = Convert.ToInt32(db.ExecuteScalar(o_dbCommand)) + 1


        Catch ex As Exception
            o_Transaction.Rollback()
            Return idSolicitud
        Finally
            o_Cnx.Close()
        End Try

        Return idSolicitud


    End Function

    Public Function GetList() As System.Data.DataTable Implements DIL.ClienteDI.GetList
        Dim db As Database = DatabaseFactory.CreateDatabase()

        Dim query As String = "select * from Solicitud where estado = 0"

        Dim dbCommand As DbCommand = db.GetSqlStringCommand(query)

        Dim productsDataSet As DataSet = Nothing

        productsDataSet = db.ExecuteDataSet(dbCommand)

        Return productsDataSet.Tables(0)
    End Function

    Public Function List() As System.Data.DataTable Implements DIL.ClienteDI.List
        Dim dr As DbDataReader = Nothing

        Dim o_BECollec_Solicitud_BE As New SolicitudBECollection

        Dim o_Solicitud_BE As SolicitudBE

        Dim db As Database = DatabaseFactory.CreateDatabase()

        Dim query As String = "select * from Clientes"

        Dim dbCommand As DbCommand = db.GetSqlStringCommand(query)

        Dim solicitudDataSet As DataSet = Nothing

        solicitudDataSet = db.ExecuteDataSet(dbCommand)
        Return solicitudDataSet.Tables(0)
    End Function
End Class
